import pandas as pd
import numpy as np

# join
# df1 = pd.DataFrame(np.arange(12).reshape((3, 4)), index=['a', 'b', 'c'], columns=['A', 'B', 'C', 'D'])
# print(df1)
#
# df2 = pd.DataFrame(np.arange(15).reshape((5, 3)), index=['a', 'c', 'd', 'e', 'f'], columns=['D', 'F', 'G'])
# print(df2)
#
# df = df1.join(df2, lsuffix='l',rsuffix='r')  # suffix后缀的意思
# print(df)
#
# df = df1.join(df2, how='right', lsuffix='l',rsuffix='r')
# print(df)
#
# df = df1.join(df2, how='inner', lsuffix='l',rsuffix='r')
# print(df)

np.random.seed(1)
df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value': np.random.randn(4)})
df2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'], 'value': np.random.randn(4)})
print(df1)
print(df2)

# join
df = df1.join(df2, lsuffix='_l', rsuffix='_r')
print(df)

# merge
df = df1.merge(df2, on='key')  # 默认内连接
print(df)

df = df1.merge(df2, on='key', how='left')  # 左连接
print(df)

df = df1.merge(df2, on='key', how='right')
print(df)


# 通过merge实现join的效果应该如何实现
# 连接后会比join多一列，这一列是指定的条件列

df = df1.merge(df2, how='left', left_on=df1.index, right_on=df2.index, suffixes=('_l', '_r'))
print(df)
